using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Xml.Linq;
using System.Configuration;
using System.Data.SqlClient;
namespace Connection
{
    public class Connection
    {


        private static string ConnString = ConfigurationManager.ConnectionStrings["ConString"].ToString();
        public static void Ejecutar(string storedProcedure, parametrosArray parameters)
        {
            using (SqlConnection conexion = new SqlConnection(ConnString))
            {
                SqlCommand Comando = new SqlCommand();
                string stored = storedProcedure;
                parametrosArray pa = new parametrosArray();
                pa = parameters;
                Comando.Parameters.Clear();
                Comando.CommandType = CommandType.StoredProcedure;
                Comando.Connection = conexion;
                Comando.CommandText = stored;
                foreach (SqlParameter p in pa.Parametros)
                {
                    Comando.Parameters.Add(p);
                }
                try
                {
                    conexion.Open();
                    Comando.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                    //throw new ConnectionException(storedProcedure + " - " + ex.Message);
                }
                finally
                {
                    pa.clear();
                    Comando.Dispose();
                    if (conexion.State == ConnectionState.Open)
                    {
                        conexion.Close();
                    }
                }
            }
        }

        public static DataSet Traer(string storedProcedure, parametrosArray parameters)
        {
            using (SqlConnection conexion = new SqlConnection(ConnString))
            {
                SqlCommand Comando = new SqlCommand();
                string stored = storedProcedure;
                parametrosArray pa = parameters;
                Comando.CommandType = CommandType.StoredProcedure;
                Comando.Connection = conexion;
                Comando.CommandText = stored;
                Comando.CommandTimeout = 200;
                foreach (SqlParameter p in pa.Parametros)
                {
                    Comando.Parameters.Add(p);
                }
                try
                {
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        da.SelectCommand = Comando;
                        using (DataSet ds = new DataSet())
                        {
                            ds.Clear();
                            da.Fill(ds);
                            return ds;
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                    //throw new ConnectionException(storedProcedure + " - " + ex.Message);
                }
                finally
                {
                    pa.clear();
                    Comando.Dispose();
                    if (conexion.State == ConnectionState.Open)
                    {
                        conexion.Close();
                    }
                }
            }
        }

        public static DataSet Traer(string storedProcedure)
        {
            return Traer(storedProcedure, new parametrosArray());
        }

        public static void Ejecutar(string storedProcedure)
        {
            Ejecutar(storedProcedure, new parametrosArray());
        }



    }
}
